# _*_ coding:utf-8 _*_
# @Time  : 2022.06.15
# @Author: zizlee

import openpyxl
from openpyxl import styles as openpyxl_styles
from openpyxl.drawing.image import Image as ODImage
from openpyxl.drawing.xdr import XDRPositiveSize2D
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, OneCellAnchor
from openpyxl.worksheet.page import PageMargins
from openpyxl.utils.units import pixels_to_EMU

report_data = {
    'report_title': '棉花(纱)产业日报',
    'report_date': '2022-06-15',
    'author': {
        'name': '张大三',
        'certificate1': 'F3030669',
        'certificate2': 'Z0015587',
    },
    'note': '数据来源第三方（wind，同花顺，棉花信息网、棉花协会网），观点仅供参考。市场有风险，投资需谨慎！',
    'index_categories': [
        {
            'category_name': '期货市场',
            'indexes': [
                {
                    'index_name': '期货市场指标1',
                    'value': 100,
                    'diff': -20,
                },
                {
                    'index_name': '期货市场指标2',
                    'value': 100,
                    'diff': 20,
                },
                {
                    'index_name': '期货市场指标3',
                    'value': 580,
                    'diff': -38,
                },{
                    'index_name': '期货市场指标4',
                    'value': 1220,
                    'diff': 600,
                },{
                    'index_name': '期货市场指标5',
                    'value': 11200,
                    'diff': 28,
                }
            ]
        },{
            'category_name': '现货市场',
            'indexes': [
                {
                    'index_name': '现货市场指标1',
                    'value': 100,
                    'diff': -20,
                },
                {
                    'index_name': '现货市场指标2',
                    'value': 100,
                    'diff': 20,
                },
                {
                    'index_name': '现货市场指标3',
                    'value': 500,
                    'diff': 50,
                }
            ]
        }
    ],
    'msg_categories': [
        {
            'category_name': '行业消息',
            'msg': '这是行业消息！'
        },{
            'category_name': '观点总结',
            'msg': '今日关注的消息，今日关注的消息今日关注的消息今日关注的消息今日关注的消息今日关注的消息今日关注的消息今日关注的消息今日关注的消息！'
        },{
            'category_name': '提示关注',
            'msg': '今日关注的消息！'
        }
    ]
}

TITLE_FONT_SIZE = 20
DATE_FONT_SIZE = 14
AUTHOR_FONT_SIZE = 8
TABLE_HEADER_FONT_SIZE = 12
TABLE_CONTENT_FONT_SIZE = 9
NOT_FONT_SIZE = 7

DEFAULT_FONT_NAME = '微软雅黑'
AUTHOR_FONT_NAME = '等线'
TABLE_HEADER_FONT_NAME = '等线'
TABLE_CONTENT_FONT_NAME = '微软雅黑'
NOTE_FONT_NAME = '等线'


def cell_font(size=11, color='000000', bold=False, name=None):
    return openpyxl_styles.Font(size=size, color=color, bold=bold, name=name)


def cell_alignment(horizontal=None, vertical=None, wrap_text=None):
    return openpyxl_styles.Alignment(horizontal=horizontal, vertical=vertical, wrap_text=wrap_text)


def cell_forward_color(color="ffffff"):
    # patternType='solid' # 纯色填充 bgColor背景色,fgColor 前景色
    return openpyxl_styles.PatternFill(patternType='solid', fgColor=color)


def cell_border(left=None, top=None, right=None, bottom=None):
    # 边框样式，可选dashDot、dashDotDot、dashed、dotted、double、hair、medium、mediumDashDot、
    # mediumDashDotDot、mediumDashed、slantDashDot、thick、thin
    side = openpyxl_styles.Side
    colors = openpyxl_styles.colors
    return openpyxl_styles.Border(top=side(style=top, color=colors.BLACK),
                                  left=side(style=left, color=colors.BLACK),
                                  right=side(style=right, color=colors.BLACK),
                                  bottom=side(style=bottom, color=colors.BLACK))


def get_diff_value_text_color(value):
    if str(value) in ['NAN', 'ERR', '0']:
        return str(value), '000000'
    else:
        return (f'{value}↑', 'ff0000') if float(value) > 0 else (f'{value}↓', '0000ff')


def create_daily_report():
    work_book = openpyxl.Workbook()
    sheet = work_book.create_sheet('产业日报', 0)
    # 页边距
    sheet.page_margins = PageMargins(top=1, left=0.3, bottom=1, right=0.3, header=1, footer=1)
    # 列宽
    sheet.column_dimensions['A'].width = 9
    sheet.column_dimensions['B'].width = 27
    sheet.column_dimensions['E'].width = 27
    sheet.column_dimensions['C'].width = 7
    sheet.column_dimensions['D'].width = 8
    sheet.column_dimensions['F'].width = 7
    sheet.column_dimensions['G'].width = 9

    # logo
    company_logo = ODImage('./reportImg/company_logo.png')
    company_logo.width, company_logo.height = (120, 42)
    sheet.add_image(company_logo, 'A1')

    # 标题
    sheet['B1'] = report_data['report_title']
    sheet['B1'].font = cell_font(size=TITLE_FONT_SIZE, bold=True, name=DEFAULT_FONT_NAME)
    sheet['B1'].alignment = cell_alignment(horizontal='center', vertical='center')
    sheet.merge_cells('B1:E1')
    # 日期
    sheet['F1'] = report_data['report_date']
    sheet['F1'].font = cell_font(size=DATE_FONT_SIZE, bold=True, name=DEFAULT_FONT_NAME)
    sheet['F1'].alignment = cell_alignment(horizontal='right', vertical='center')
    sheet.merge_cells('F1:G1')

    # 作者
    sheet['A2'] = f'撰写人:{report_data["author"]["name"]}    从业资格证号:{report_data["author"]["certificate1"]}    投资咨询从业证书号:{report_data["author"]["certificate2"]}'
    sheet['A2'].font = cell_font(size=AUTHOR_FONT_SIZE, bold=True, name=AUTHOR_FONT_NAME)
    sheet['A2'].alignment = cell_alignment(horizontal='left', vertical='center')
    sheet.merge_cells('A2:G2')

    # 表格标题
    sheet['A3'] = '项目类别'
    sheet['A3'].font = cell_font(size=TABLE_HEADER_FONT_SIZE, color='ffffff', bold=True, name=TABLE_HEADER_FONT_NAME)
    # sheet['A3'].fill = cell_forward_color(color='0f6fc6')
    sheet['A3'].border = cell_border(top='medium')
    texts = ['数据指标', '最新', '环比']
    for i, col in enumerate(['B', 'C', 'D', 'E', 'F', 'G']):
        sheet[f'{col}3'] = texts[i % 3]
        sheet[f'{col}3'].font = cell_font(size=TABLE_HEADER_FONT_SIZE, color='ffffff', bold=True, name=TABLE_HEADER_FONT_NAME)
        # sheet[f'{col}3'].fill = cell_forward_color(color='0f6fc6')
        sheet[f'{col}3'].border = cell_border(top='medium')
        if i % 3 == 0:
            sheet[f'{col}3'].alignment = cell_alignment(horizontal='center', vertical='center')
        else:
            sheet[f'{col}3'].alignment = cell_alignment(horizontal='right', vertical='center')

    # 写入指标数据,返回已使用的行号
    current_row = write_indexes_values(sheet, start_row=4)
    # 写入最后的备注
    sheet[f'A{current_row}'] = report_data['note']
    sheet[f'A{current_row}'].border = cell_border(bottom='medium', top='medium')
    sheet[f'A{current_row}'].font = cell_font(size=NOT_FONT_SIZE, bold=False, name=NOTE_FONT_NAME)
    sheet[f'A{current_row}'].alignment = cell_alignment(horizontal='left', vertical='center')
    sheet.merge_cells(f'A{current_row}:G{current_row}')

    # 前景色填充
    for letter in ['A', 'B', 'C', 'D', 'E', 'F', 'G']:  # A-G
        for col in range(1, current_row):
            sheet[f'{letter}{col}'].fill = cell_forward_color(color='ffffff')
            if ord(letter) <= ord('G') and col == 3:
                sheet[f'{letter}{col}'].fill = cell_forward_color(color='0f6fc6')

    work_book.save('日报.xlsx')


def write_indexes_values(sheet, start_row):
    category_row = start_row - 1  # 从第四行开始写,因为一开始+1，此处=3
    for category_item in report_data['index_categories']:
        category_row += 1
        c_start_row = category_row
        sheet[f'A{category_row}'] = category_item['category_name']
        sheet[f'A{category_row}'].font = cell_font(size=TABLE_CONTENT_FONT_SIZE, name=TABLE_CONTENT_FONT_NAME)
        sheet[f'A{category_row}'].alignment = cell_alignment(horizontal='center', vertical='center')
        sheet[f'A{category_row}'].border = cell_border(right='medium', bottom='medium')
        for i, index_item in enumerate(category_item['indexes']):
            if i > 0 and i % 2 == 0:
                category_row += 1
            if i % 2 == 0:
                sheet[f'B{category_row}'] = index_item['index_name']
                sheet[f'B{category_row}'].font = cell_font(size=TABLE_CONTENT_FONT_SIZE, name=TABLE_CONTENT_FONT_NAME)
                sheet[f'C{category_row}'] = index_item['value']
                sheet[f'C{category_row}'].font = cell_font(size=TABLE_CONTENT_FONT_SIZE, bold=True, name=TABLE_CONTENT_FONT_NAME)
                diff_value, diff_color = get_diff_value_text_color(index_item['diff'])
                sheet[f'D{category_row}'] = diff_value
                sheet[f'D{category_row}'].font = cell_font(size=TABLE_CONTENT_FONT_SIZE, bold=True, color=diff_color, name=TABLE_CONTENT_FONT_NAME)
                sheet[f'D{category_row}'].alignment = cell_alignment(horizontal='right', vertical='center')
                sheet[f'D{category_row}'].border = cell_border(right='dashed')
            else:
                sheet[f'E{category_row}'] = index_item['index_name']
                sheet[f'E{category_row}'].font = cell_font(size=TABLE_CONTENT_FONT_SIZE, name=TABLE_CONTENT_FONT_NAME)
                sheet[f'F{category_row}'] = index_item['value']
                sheet[f'F{category_row}'].font = cell_font(size=TABLE_CONTENT_FONT_SIZE, bold=True, name=TABLE_CONTENT_FONT_NAME)
                diff_value, diff_color = get_diff_value_text_color(index_item['diff'])
                sheet[f'G{category_row}'] = diff_value
                sheet[f'G{category_row}'].font = cell_font(size=TABLE_CONTENT_FONT_SIZE, bold=True, color=diff_color, name=TABLE_CONTENT_FONT_NAME)
                sheet[f'G{category_row}'].alignment = cell_alignment(horizontal='right', vertical='center')

            for col in ['B', 'C', 'E', 'F']:
                sheet[f'{col}{category_row}'].alignment = cell_alignment(vertical='center')  # 垂直居中

        # 下边框
        for col in ['B', 'C', 'D', 'E', 'F', 'G']:
            sheet[f'{col}{category_row}'].border = cell_border(bottom='medium')
            if col == 'D':  # 右侧的点分割线
                sheet[f'{col}{category_row}'].border = cell_border(bottom='medium', right='dashed')
        # 合并类别名称单元格
        sheet.merge_cells(f'A{c_start_row}:A{category_row}')

    for i, category_item in enumerate(report_data['msg_categories']):
        category_row += 1
        sheet[f'A{category_row}'] = category_item['category_name']
        sheet[f'A{category_row}'].font = cell_font(size=TABLE_CONTENT_FONT_SIZE, name=TABLE_CONTENT_FONT_NAME)
        sheet[f'A{category_row}'].alignment = cell_alignment(horizontal='center', vertical='center')
        sheet[f'A{category_row}'].border = cell_border(top='medium', right='medium', bottom='medium')
        sheet[f'B{category_row}'] = category_item['msg']
        sheet[f'B{category_row}'].alignment = cell_alignment(horizontal='left', vertical='top', wrap_text=True)
        sheet[f'B{category_row}'].border = cell_border(bottom='medium')
        sheet[f'B{category_row}'].font = cell_font(size=TABLE_CONTENT_FONT_SIZE, name=TABLE_CONTENT_FONT_NAME)
        if i == 0:
            sheet.merge_cells(f'B{category_row}:F{category_row}')  # 下边框
            # 贴二维码1
            qrcode1 = ODImage('./reportImg/yjyqrcode.png')
            qrcode1.width, qrcode1.height = (90, 65)
            # 计算出图片在excel中位置，是图片的大小
            size = XDRPositiveSize2D(pixels_to_EMU(qrcode1.height), pixels_to_EMU(qrcode1.width))
            # 相对于单元格内的(x,y,top,left)偏移量,行从0计数，此处-1
            marker = AnchorMarker(colOff=pixels_to_EMU(0), rowOff=pixels_to_EMU(5), row=category_row-1, col=6)
            qrcode1.anchor = OneCellAnchor(_from=marker, ext=size)
            sheet.add_image(qrcode1)

            sheet[f'G{category_row}'].alignment = cell_alignment(horizontal='center', vertical='top')
            sheet[f'G{category_row}'].border = cell_border(bottom='medium')
            sheet.row_dimensions[category_row].height = 90  # 行高
        elif i == 1:
            sheet.merge_cells(f'B{category_row}:F{category_row}')  # 下边框
            # 贴二维码2
            qrcode2 = ODImage('./reportImg/wxkfqrcode.png')
            qrcode2.width, qrcode2.height = (90, 65)
            # 计算出图片在excel中位置，是图片的大小
            size2 = XDRPositiveSize2D(pixels_to_EMU(qrcode2.height), pixels_to_EMU(qrcode2.width))
            # 相对于单元格内的(x,y,top,left)偏移量,行从0计数，此处-1
            marker = AnchorMarker(colOff=pixels_to_EMU(0), rowOff=pixels_to_EMU(5), row=category_row - 1, col=6)
            qrcode2.anchor = OneCellAnchor(_from=marker, ext=size2)
            sheet.add_image(qrcode2)

            sheet[f'G{category_row}'].alignment = cell_alignment(horizontal='center', vertical='top')
            sheet[f'G{category_row}'].border = cell_border(bottom='medium')
            sheet.row_dimensions[category_row].height = 90
        else:
            sheet.merge_cells(f'B{category_row}:G{category_row}')

    return category_row + 1


if __name__ == '__main__':
    create_daily_report()
